package com.yict.common.query.core;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.yict.common.exception.ServiceException;
import com.yict.common.query.bean.CommonSearchBean;
import com.yict.common.query.bean.CondictionsBean;
import com.yict.common.util.AssertUtil;
import com.yict.common.util.DateUtil;
import com.yict.common.util.ListUtil;
import com.yict.common.util.StringUtil;

/**
 * <h3>Class name</h3> 多条件查询工具 <h4>Description</h4>
 * 
 * <h4>Special Notes</h4>
 * 
 * 
 * @version 1.2
 * @author Jay.Wu 2010-1-2
 * 
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public class QueryUtil {
	private static final Log log = LogFactory.getLog(QueryUtil.class);

	private List<CondictionsBean> appendCondictions;

	private List<CondictionsBean> condictions;

	private PageUtil pageUtil;

	private List<Object> queryValues;

	private Object[] values;

	private String queryKey;

	private String queryType;

	private String queryString;

	private Long queryId;

	private Map valuesMap;

	private boolean able2Add;

	private String keyModel;

	private CommonSearchBean searchBean;

	private Map condictionsBeanMap;

	private String appendGlobalRelation;

	private String orderBy;

	private final static String blank = "";

	/**
	 * 问号参数模式
	 */
	public final static String KEY_MODEL_QUESTION_MARK = "?";

	/**
	 * 冒号参数模式
	 */
	public final static String KEY_MODEL_COLON = ":";

	/**
	 * 无参数模式
	 */
	public final static String KEY_MODEL_NONE = "no_key";

	public QueryUtil() {
		able2Add = true;
		condictions = new ArrayList<CondictionsBean>();
		appendCondictions = new ArrayList<CondictionsBean>();
		queryType = "hql";
		valuesMap = new HashMap();
		keyModel = KEY_MODEL_QUESTION_MARK;
		appendGlobalRelation = "and";
		queryValues = new ArrayList<Object>();
	}

	public QueryUtil(PageUtil pageUtil) {
		this();
		this.pageUtil = pageUtil;
	}

	public void clear() {
		condictions.clear();
	}

	private void addBean(CondictionsBean bean) {
		if (isAble2Add()) {
			condictions.add(bean);
		}
	}

	private void addAppendBean(CondictionsBean bean) {
		if (isAble2Add()) {
			appendCondictions.add(bean);
		}
	}

	public void put(Object key, Object value) {
		valuesMap.put(key, value);
	}

	/**
	 * 快速增加bean，完整
	 * 
	 * @param property
	 * @param value
	 * @param anotherValue
	 * @param startStr
	 * @param endStr
	 * @return
	 */
	public CondictionsBean addBetween(String property, Object value, Object anotherValue) {
		CondictionsBean bean = new CondictionsBean();
		bean.setProperty(property);
		bean.setValue(value);
		bean.setAnotherValue(anotherValue);
		addBean(bean);
		return bean;
	}

	public CondictionsBean append(String property, Object value) {
		CondictionsBean bean = new CondictionsBean();
		bean.setProperty(property);
		bean.setValue(value);
		addAppendBean(bean);
		return bean;
	}

	public CondictionsBean append(String property, Object value, String condiction) {
		CondictionsBean bean = new CondictionsBean();
		bean.setProperty(property);
		bean.setValue(value);
		bean.setCondiction(condiction);
		addAppendBean(bean);
		return bean;
	}

	/**
	 * 快速增加bean，精简
	 * 
	 * @param property
	 * @param value
	 * @return
	 */
	public CondictionsBean add(String property, Object value) {
		CondictionsBean bean = new CondictionsBean();
		bean.setProperty(property);
		bean.setValue(value);
		addBean(bean);
		return bean;
	}

	public CondictionsBean add(String property, Object value, int pos) {
		CondictionsBean bean = new CondictionsBean();
		bean.setPos(pos);
		bean.setProperty(property);
		bean.setValue(value);
		addBean(bean);
		return bean;
	}

	/**
	 * 分离动态查询条件
	 * 
	 * @param queryString
	 * @param condictionsMap
	 * @return
	 */
	private String analyseQueryString(String queryString, Map condictionsMap) {
		queryString = queryString.replace("\n", " ");
		String regex = "\\$\\[(.+?)\\]";
		Matcher m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(queryString);
		int keyId = 0;

		while (m.find()) {
			try {
				String key0 = m.group(0);
				String key1 = m.group(1);
				String mapKey = "$[" + key1 + "#" + keyId + "]";

				condictionsMap.put(mapKey, key1);
				queryString = StringUtil.replaceFirst(queryString, key0, mapKey);
				keyId++;
			}

			catch (Exception e) {
				continue;
			}
		}

		return queryString;
	}

	/**
	 * 动态组装查询语句入口
	 * 
	 * @param queryString
	 * @param values
	 * @author Jay Wu
	 * @return
	 * @throws Exception
	 */
	public String combineQueryString() {
		if (queryString == null) {
			AssertUtil.errorWithBundle("E_its_common_null_queryString", ServiceException.class);
		}

		// 初始化condictions为Map
		condictionsBeanMap = new HashMap();

		for (Iterator iterator = condictions.iterator(); iterator.hasNext();) {
			CondictionsBean bean = (CondictionsBean) iterator.next();
			if (bean.getValue() != null) {
				condictionsBeanMap.put(bean.getProperty(), bean);
			}
		}

		queryString = StringUtil.replaceKeysSimple(valuesMap, queryString);

		if (StringUtil.countString(queryString, "?") > 0) {
			keyModel = KEY_MODEL_QUESTION_MARK;
		} else {
			String regex = "(:)([\\w|\\d]*)";
			Matcher m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(queryString);

			if (m.find()) {
				keyModel = KEY_MODEL_COLON;
			} else {
				keyModel = KEY_MODEL_NONE;
			}
		}

		// LinkedHashMap来保证values的原始顺序
		Map queryStringMap = new LinkedHashMap();
		queryString = analyseQueryString(queryString, queryStringMap);

		// 处理固定和动态问号参数定义
		convertNomalCondictions(queryStringMap);
		convertKeyCondictions(queryStringMap);

		if (values == null) {
			// 参数为空则清空所有条件
			for (Iterator iterator = queryStringMap.keySet().iterator(); iterator.hasNext();) {
				String key = (String) iterator.next();
				queryStringMap.put(key, blank);
			}
		}

		// 依次替换处理后的条件
		for (Iterator iterator = queryStringMap.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			String value = (String) queryStringMap.get(key);

			if (StringUtil.isNotBlank(value)) {
				value = " " + value.trim() + " ";
			}

			queryString = queryString.replace(key, value);
		}

		if (keyModel.equals(KEY_MODEL_COLON)) {
			disposeKeyCondictions();
		}

		queryValues = ListUtil.Array2List(values);
		appendCondictions();

		if (StringUtil.isNotBlank(orderBy)) {
			queryString = queryString + " order by " + orderBy;
		}

		log.debug("\nqueryKey: " + queryKey + " \n" + queryString + "\nkeyModel: " + keyModel + "\nvalues: "
				+ queryValues);
		return queryString;
	}

	/**
	 * 问号参数类型：values处理条件
	 * 
	 * @param condictionsMap
	 * @param newValues
	 * @throws Exception
	 */
	private void disposeNomalCondictions(Map condictionsMap) {
		if (keyModel.equals(KEY_MODEL_COLON)) {
			return;
		}

		// 实际参数数目
		int valuesNum = 0;

		for (Iterator iterator = condictionsMap.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			boolean clear = false;

			if (key.contains("()")) {
				condictionsMap.put(key, blank);
				clear = true;
			}

			for (int j = 0; j < key.length(); j++) {
				// 找到了参数入口
				if (key.charAt(j) == '?') {
					// 只要有一个参数为空，则该条件不能加入查询中
					if (values[valuesNum++] == null) {
						clear = true;
						condictionsMap.put(key, blank);
					}
				}
			}

			// 将这个括号内的所有值清空
			if (clear) {
				// 统计这个括号内有多少问号
				int num = StringUtil.countString(key, "?");
				while (num > 0) {
					values[valuesNum - num] = null;
					num--;
				}
			}
		}

		// 传入参数与查询条件需要的参数不一致，抛出异常
		if (values.length != valuesNum) {
			// AssertUtil.errorWithBundle("E_its_common_null_valuesDiffer",
			// BizException.class);
		}

		List newValues = new ArrayList();

		for (int i = 0; i < values.length; i++) {
			if (values[i] != null) {
				newValues.add(values[i]);
			}
		}

		values = newValues.toArray();
	}

	/**
	 * key参数类型(:key)：根据传入的property动态组装values和条件
	 * 
	 * @param condictionsMap
	 * @param newValues
	 * @throws Exception
	 */
	private void convertKeyCondictions(Map condictionsMap) {
		if (!keyModel.equals(KEY_MODEL_COLON)) {
			return;
		}

		// 参数key的正则表达式，如：":name"
		String regex = "(:)([\\w|\\d]*)";
		condictions.clear();

		for (Iterator iterator = condictionsMap.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			// 该动态条件是否清除
			boolean clear = false;
			// 该动态中包含的参数key
			List targets = new ArrayList();

			// 过滤空的固定条件，如 a in()
			if (key.contains("()")) {
				condictionsMap.put(key, blank);
				clear = true;
			} else {
				// 从动态条件中找到参数key
				Matcher m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(key);

				while (m.find()) {
					// 找到参数key后，查找condictionsBeanMap，看是存在满足条件的Condictionbean
					String target = m.group(0);
					CondictionsBean bean = (CondictionsBean) condictionsBeanMap.get(target);

					// 不存在CondictionsBean，则该条查询条件被移除
					if (bean == null) {
						clear = true;
						condictionsMap.put(key, blank);
					} else {
						// 该CondictionsBean被引用一次
						bean.addQuoteCount();
						// 将该条动态条件中出现的参数key保留到targets
						targets.add(bean);
					}
				}
			}

			// 如果此次循环的查询条件需要被清除，则查询条件中包含的CondictionsBean的引用次数需要减去
			if (clear) {
				for (Iterator targetsIter = targets.iterator(); targetsIter.hasNext();) {
					CondictionsBean bean = (CondictionsBean) targetsIter.next();
					bean.removeQuoteCount();
				}
			}
		}

		// 重新整理condictions，将被引用的CondictionsBean加入其中
		List newValues = new ArrayList();
		for (Iterator iterator = condictionsBeanMap.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			CondictionsBean bean = (CondictionsBean) condictionsBeanMap.get(key);

			// 判断参数key是否被其他条件引用，若有，则加入该CondictionsBean
			if (bean.getQuoteCount() > 0) {
				condictions.add(bean);
				newValues.add(bean.getValue());
			}
		}

		values = newValues.toArray();
	}

	private void disposeKeyCondictions() {
		// 参数key的正则表达式，如：":name"
		String regex = "(:)([\\w|\\d]*)";

		// 从动态条件中找到参数key
		Matcher m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(queryString);
		List newValues = new ArrayList();

		while (m.find()) {
			// 找到参数key后，查找condictionsBeanMap，看是存在满足条件的Condictionbean
			String target = m.group(0);
			CondictionsBean bean = (CondictionsBean) condictionsBeanMap.get(target);

			// 不存在CondictionsBean，则该条查询条件被移除
			if (bean == null) {
				AssertUtil.errorWithBundle("error_CondictionsBean_match", ServiceException.class);
			} else {
				queryString = StringUtil.replaceFirst(queryString, target, "?");
				newValues.add(bean.getValue());
			}
		}

		values = newValues.toArray();
	}

	/**
	 * 问号参数类型：根据传入的property动态组装带有顺序的values
	 * 
	 * @param queryStringMap
	 * 
	 * @param queryString
	 * @return
	 * @throws Exception
	 */
	private Object[] convertNomalCondictions(Map queryStringMap) {
		values = null;

		if (!keyModel.equals(KEY_MODEL_QUESTION_MARK)) {
			return values;
		}

		// 统计问号的个数，即参数的个数
		int valuesNum = StringUtil.countString(queryString, "?");
		values = new Object[valuesNum];
		Map condictionsMap = new HashMap();
		Object[] condictionObjs = new Object[condictions.size()];

		// 利用list，为condictions里面的CondictionsBean按pos排序
		for (int i = 0; i < condictions.size(); i++) {
			CondictionsBean bean = (CondictionsBean) condictions.get(i);
			String property = bean.getProperty();

			List sameNameCondictions = (List) condictionsMap.get(property);

			if (sameNameCondictions == null) {
				// 将sameNameCondictions根据condictionObjs的size来初始化容量
				sameNameCondictions = ListUtil.Array2List(condictionObjs);
				condictionsMap.put(property, sameNameCondictions);
			}

			// CondictionsBean的pos为-1，则表示该查询参数没有重复
			if (bean.getPos() == -1) {
				sameNameCondictions.set(0, bean);
			} else {
				// 根据pos的位置来定位CondictionsBean
				sameNameCondictions.set(bean.getPos(), bean);
			}
		}

		// 根据queryString中的问号来定位每个CondictionsBean的顺序
		valuesNum = 0;
		for (int i = 0; i < queryString.length(); i++) {
			// 找到了问号，即参数入口
			if (this.queryString.charAt(i) == '?') {
				// 最靠近问号的CondictionsBean位置
				int propertyPos = -1;

				CondictionsBean bean = null;
				int temp = queryString.length();

				// 遍历所有CondictionsBean，找出最靠近问号的CondictionsBean
				for (Iterator iterator = condictionsMap.keySet().iterator(); iterator.hasNext();) {
					String key = (String) iterator.next();
					List sameNameCondictions = (List) condictionsMap.get(key);

					if (ListUtil.isEmpty(sameNameCondictions)) {
						continue;
					}

					CondictionsBean sameBean = (CondictionsBean) sameNameCondictions.get(0);

					// 如果sameBean为空，则表示之前已经被引用过了，不能再次被引用
					if (sameBean == null) {
						continue;
					}

					// 找出靠近问号的CondictionsBean位置
					temp = StringUtil.lastIndexOf(queryString.substring(0, i), key, 1);
					if (temp > 0) {
						// 防止误匹配现象：如SQL中条件为 aa.title = ?，但key值为a.title。
						String validateStr = queryString.substring(temp - 1, temp);
						if (StringUtil.isNumeric(validateStr) || StringUtil.isLetter(validateStr)) {
							continue;
						}
					}

					// 如果当前CondictionsBean比之前的CondictionsBean靠近问号，则取最靠近那个
					if (temp > propertyPos) {
						propertyPos = temp;
						bean = sameBean;
					}

				}

				// 找到了最靠近问号的CondictionsBean
				if (bean != null) {
					// 因为程序是从左到右按顺序扫描queryString，所以若CondictionsBean有重复，则最先出现的应该是pos最小的。
					// sameNameCondictions在初始化的时候已经排序了，所以最小的就是index为0的CondictionsBean。
					List sameNameCondictions = (List) condictionsMap.get(bean.getProperty());
					CondictionsBean sameBean = (CondictionsBean) sameNameCondictions.get(0);
					values[valuesNum++] = sameBean.getValue();

					// 被引用过的CondictionsBean应该被移除
					sameNameCondictions.remove(0);
				} else {
					// 没有找到符合条件的CondictionsBean
					values[valuesNum++] = null;
				}
			}
		}

		// 参数数组不为空，则智能判断是否要加入该条件
		if (values != null) {
			disposeNomalCondictions(queryStringMap);
		}

		return values;
	}

	// public static void main(String[] args) {
	// String queryString =
	// "SELECT * FROM its_sys_main_form m WHERE b.title like :title";
	// // String queryString = "SELECT b, m " +
	// // "  FROM ItsSysNoticeBO b, ItsSysMainFormBO m "
	// // + " WHERE ((b.effectiveDate<=? AND b.endDate>=?) " +
	// // " AND m.flowTemplateId = ? "
	// // // + "$[OR (b.effectiveDate>=? AND b.endDate<=?))] " +
	// // // "   $[AND m.curStageName = ?] "
	// // // + "   $[AND b.title like ?] " + "   AND m.id = b.mainFormId "
	// // + " ORDER BY b.id DESC ";
	// try {
	// QueryUtil queryUtil = new QueryUtil();
	// queryUtil.setQueryKey("abc");
	// queryUtil.add(":title", "%" + "a" + "%");
	// // queryUtil.add("b.title", "%" + "a" + "%");
	// queryUtil.add("m.flowTemplateId", "m.flowTemplateId");
	// queryUtil.add("b.effectiveDate", "a0", 0);
	// queryUtil.add("b.endDate", "ab0", 0);
	//
	// queryUtil.add("b.effectiveDate", "a1", 1);
	// queryUtil.add("b.endDate", "ab1", 1);
	//
	// queryUtil.add("m.curStageName", "ab");
	// queryUtil.setQueryString(queryString);
	// queryUtil.combineQueryString();
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	// }

	/**
	 * 自动追加查询条件
	 */
	private void appendCondictions() {
		if (ListUtil.isEmpty(appendCondictions)) {
			return;
		}

		List<CondictionsBean> temps = new ArrayList<CondictionsBean>();
		temps.addAll(appendCondictions);

		for (CondictionsBean bean : temps) {
			// 处理空字符
			if (bean.getValue() instanceof String) {
				String tempValue = (String) bean.getValue();
				if (StringUtil.isBlank(tempValue)) {
					bean.setValue(null);
				}
			}

			// 移除空值条件
			if (bean.getValue() == null && bean.getAnotherValue() == null) {
				appendCondictions.remove(bean);
				continue;
			}

			// 处理between时间
			if (bean.getValue() instanceof Date) {
				Date date = (Date) bean.getValue();
				bean.setValue(DateUtil.addDays(date, -1));
				;
				if (bean.getCondiction().equals("between") && bean.getAnotherValue() == null) {
					bean.setCondiction(">=");
				}
			}

			if (bean.getAnotherValue() instanceof Date) {
				Date calendar = (Date) bean.getAnotherValue();
				bean.setAnotherValue(calendar);
				if (bean.getCondiction().equals("between") && bean.getValue() == null) {
					bean.setValue(calendar);
					bean.setAnotherValue(null);
					bean.setCondiction("<=");
				}
			}

			// 处理like条件
			if (bean.getCondiction().equals("like")) {
				bean.setValue("%" + bean.getValue() + "%");
			}

		}

		StringBuffer whereStr = new StringBuffer("");

		boolean first = true;
		for (CondictionsBean condictionsBean : appendCondictions) {
			if (first) {
				whereStr.append(" ");
				first = false;
			} else {
				whereStr.append(appendGlobalRelation + " ");
			}

			// 处理 "is null" 条件
			if (condictionsBean.getCondiction().equals("is") && condictionsBean.getValue().equals("null")) {
				whereStr.append(condictionsBean.getProperty() + " " + condictionsBean.getCondiction() + " "
						+ condictionsBean.getValue() + " ");
				continue;
			}

			whereStr.append(condictionsBean.getProperty() + " " + condictionsBean.getCondiction() + " ? ");
			queryValues.add(condictionsBean.getValue());

			// 处理between条件
			if (condictionsBean.getCondiction().equalsIgnoreCase("between")) {
				whereStr.append(" and ? ");
				queryValues.add(condictionsBean.getAnotherValue());
			}
		}

		if (whereStr.toString().trim().length() > 0) {
			queryString = queryString + " and (" + whereStr.toString() + ")";
		}
	}

	/**
	 * @return the condictions
	 */
	public List getCondictions() {
		return condictions;
	}

	/**
	 * @return the pageUtil
	 */
	public PageUtil getPageUtil() {
		return pageUtil;
	}

	/**
	 * @param pageUtil
	 *            the pageUtil to set
	 */
	public void setPageUtil(PageUtil pageUtil) {
		this.pageUtil = pageUtil;
	}

	public CommonSearchBean getSearchBean() {
		return searchBean;
	}

	public void setSearchBean(CommonSearchBean searchBean) {
		this.searchBean = searchBean;
	}

	public boolean isAble2Add() {
		return able2Add;
	}

	public void setAble2Add(boolean able2Add) {
		this.able2Add = able2Add;
	}

	public String getQueryKey() {
		return queryKey;
	}

	public void setQueryKey(String queryKey) {
		this.queryKey = queryKey;
	}

	public String getQueryType() {
		return queryType;
	}

	public void setQueryType(String queryType) {
		this.queryType = queryType;
	}

	public String getQueryString() {
		return queryString;
	}

	public void setQueryString(String queryString) {
		this.queryString = queryString;
	}

	public Map getCondictionsBeanMap() {
		return condictionsBeanMap;
	}

	public void setCondictionsBeanMap(Map condictionsBeanMap) {
		this.condictionsBeanMap = condictionsBeanMap;
	}

	public String getAppendGlobalRelation() {
		return appendGlobalRelation;
	}

	public void setAppendGlobalRelation(String appendGlobalRelation) {
		this.appendGlobalRelation = appendGlobalRelation;
	}

	public List<Object> getQueryValues() {
		return queryValues;
	}

	public void setQueryValues(List<Object> queryValues) {
		this.queryValues = queryValues;
	}

	public String getOrderBy() {
		return orderBy;
	}

	public void setOrderBy(String orderBy) {
		this.orderBy = orderBy;
	}

	public Long getQueryId() {
		return queryId;
	}

	public void setQueryId(Long queryId) {
		this.queryId = queryId;
	}
}
